/**
 * 
 */
package main.org.form.action;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.LinkedHashMap;
import java.util.Properties;
import java.util.logging.Level;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import main.org.model.Utils;

import org.adempiere.exceptions.DBException;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.interceptor.ServletResponseAware;
import org.compiere.dsr.DSR_Cell;
import org.compiere.dsr.DSR_DataCollection;
import org.compiere.model.I_BPM_FormValues;
import org.compiere.model.I_C_Project;
import org.compiere.model.MBPMForm;
import org.compiere.model.MBPMFormValues;
import org.compiere.model.MBPMProject;
import org.compiere.model.MBPMProjectAgreement;
import org.compiere.model.MRefList;
import org.compiere.model.MTable;
import org.compiere.model.MUser;
import org.compiere.model.PO;
import org.compiere.model.X_BPM_BudgetCall;
import org.compiere.model.X_BPM_FormCode;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

import com.opensymphony.xwork2.ActionContext;

/**
 * @author V.Sokolov
 * 
 */
public class FormBP extends Forms implements ServletRequestAware,
		ServletResponseAware {

	/**
	 * 
	 */
	private static final long serialVersionUID = -7222019810593858920L;
	/** Logger */
	protected CLogger log = CLogger.getCLogger(getClass());
	/* */
	public static final String sessionKey = "formBP";

	DSR_DataCollection collection;
	private HttpServletRequest request;
	private HttpServletResponse response;
	private int BPM_Form_ID = 0;
	private int BPM_SubsProject_ID = 0;
	public boolean Approved = false;
	LinkedHashMap<Integer, String> projectList = new LinkedHashMap<Integer, String>();
	/* 
	 */
	@Override
	public String execute() throws Exception {
		setAD_Client_ID(1000000);

		this.session = ActionContext.getContext().getSession();
		StringBuilder err = new StringBuilder();
		
		setMessageError(err.toString());
		if (err.length() != 0)
			return NONE;
		Forms fo = (Forms) session.get(Forms.sessionKey);
		
		Env.setContext(Env.getCtx(), "#AD_Client_ID", AD_Client_ID);
		Env.setContext(Env.getCtx(), "#AD_Org_ID", AD_Org_ID);
		Env.setContext(Env.getCtx(), "#AD_Language", getLang());
		Env.setContext(Env.getCtx(), "#AD_User_ID", AD_User_ID);
		Env.setContext(Env.getCtx(), "#BPM_VersionBudget_ID", BPM_VersionBudget_ID);
		Env.setContext(Env.getCtx(), "#BPM_Project_ID", BPM_Project_ID);
		
		fo.setPage(Utils.TEMPLATE_SECOND);
		session.put(Forms.sessionKey, fo);
		setPage(Utils.TEMPLATE_SECOND);
		
		MBPMForm form = new MBPMForm(Env.getCtx(), BPM_Form_ID, null);
		if (form.getBPM_FormCode().getCategoryName()
				.equals(X_BPM_FormCode.CATEGORYNAME_Profitability)) {

			if (BPM_Project_ID == 0)
				err.append("Не найден проект для ДЗО <br />");

			setMessageError(err.toString());
			if (err.length() != 0)
				return NONE;

			session.put(FormBP.sessionKey, this);
			
		
			setProjectList();
			
			return X_BPM_FormCode.CATEGORYNAME_Profitability;
		}

		if (err.length() == 0) {
			int BPM_Project_Agreement_ID=0;
			String sql = "SELECT BPM_Project_Agreement_ID FROM BPM_Project_Agreement " 
						+"\n WHERE BPM_Project_ID = "+BPM_Project_ID
						+"\n AND AD_Client_ID = "+AD_Client_ID
						+"\n AND AD_Org_ID = "+AD_Org_ID;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					BPM_Project_Agreement_ID = rs.getInt(1);
				}
			} catch (SQLException e) {
				CLogger.get().log(Level.INFO, sql, e);
				throw new DBException(e, sql);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
	        MTable table = MTable.get(Env.getCtx(), "BPM_Project_Agreement");
	        PO po = table.getPO (BPM_Project_Agreement_ID, null);
			if (BPM_Project_Agreement_ID==0) {
				po.set_ValueOfColumn("AD_Org_ID",AD_Org_ID);
				po.set_ValueOfColumn("BPM_Project_ID", BPM_Project_ID);
				po.set_ValueOfColumn("Description","Согласование бюджетных форм. "+new MUser(Env.getCtx(),AD_User_ID,null).getDescription());
				po.set_ValueOfColumn("AGR_Dispatcher_ID",1000003);
				po.saveEx();
			} 
			this.DocStatus = po.get_ValueAsString("DocStatus");
			if ((X_BPM_BudgetCall.DOCSTATUS_Approved.equals(DocStatus)||
					X_BPM_BudgetCall.DOCSTATUS_OnApproval.equals(DocStatus))&&(AD_User_ID!=1000060))
				this.ReadOnly=true;
			else this.ReadOnly=false;
			if (AD_Org_ID==0) this.ReadOnly=true;
			Timestamp created = null;
			sql = "SELECT MAX(created) FROM AGR_AgreementList \n"
				+ "WHERE AD_Table_ID = " + table.get_ID()
				+ " AND Record_ID = " + po.get_ID();
			pstmt = null;
			rs = null;
			try {
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					created = rs.getTimestamp(1);
				}
			} catch (SQLException e) {
				CLogger.get().log(Level.INFO, sql, e);
				throw new DBException(e, sql);
			} finally {
				DB.close(rs, pstmt);
				rs = null;
				pstmt = null;
			}
			
			DSR_DataCollection collection = 
				new DSR_DataCollection(BPM_Form_ID,	BPM_Project_ID, AD_Org_ID, AD_Client_ID, created);
			setCollection(collection);
			session.put(FormBP.sessionKey, this);
			session.put(Forms.sessionKey, this);
			return SUCCESS;
		} else
			return NONE;
	}
	public String loadProjectData() throws Exception{
		
		String sss = (String) request.getAttribute("ProjectSubID");
		System.out.println(sss);
		return SUCCESS;
		}
	public DSR_DataCollection getCollection() {
		return this.collection;
	}


	public HttpServletResponse getServletResponse() {
		return response;
	}

	/*
	 */
	@Override
	public String input() throws Exception {
		setAD_Client_ID(1000000);

		this.session = ActionContext.getContext().getSession();
		StringBuilder err = new StringBuilder();

		Env.setContext(Env.getCtx(), "#AD_Client_ID", getAD_Client_ID());
		Env.setContext(Env.getCtx(), "#AD_Org_ID", getAD_Org_ID());
		Env.setContext(Env.getCtx(), "#AD_User_ID", getAD_User_ID());
		Env.setContext(Env.getCtx(), "#BPM_VersionBudget_ID", getBPM_VersionBudget_ID());
		Env.setContext(Env.getCtx(), "#BPM_Project_ID", getBPM_Project_ID());
		
		//
		int BPM_Project_Agreement_ID = 0;
		MBPMProject project = new MBPMProject(Env.getCtx(), getBPM_Project_ID(), null);
		//Проверка текущего проекта на закрытость (согласованный или нет)
		BPM_Project_Agreement_ID = DB.getSQLValue(null, "SELECT bpm_project_agreement_ID FROM bpm_project_agreement " +
				"WHERE BPM_Project_ID = ? AND AD_Org_ID = ? AND IsApproved IS NULL"
				, project.get_ID(), Env.getAD_Org_ID(Env.getCtx()));
		
		if(BPM_Project_Agreement_ID<1) //если проект согласован тогда надо проверить не создан ли новый проект унаследованный от текущего
			BPM_Project_Agreement_ID = DB.getSQLValue(null, "SELECT BPM_Project_Agreement_ID FROM BPM_Project_Agreement " +
					"WHERE AD_org_ID = "+Env.getAD_Org_ID(Env.getCtx())+" AND BPM_Project_ID IN " +
					"(SELECT BPM_Project_ID FROM BPM_Project WHERE BPM_Parent_ID = "+project.get_ID()+") AND IsApproved IS NULL");
		if(BPM_Project_Agreement_ID>0) // если есть унаследованный проект
			BPM_Project_ID = new MBPMProjectAgreement(Env.getCtx(), BPM_Project_Agreement_ID, null).getBPM_Project_ID(); //устанавливаем его как текущий проект
			
		if(BPM_Project_Agreement_ID<1)
		{
			MBPMProject newVProject = new MBPMProject(Env.getCtx(), 0, null);
			newVProject.setName(project.getName()+" от "+new SimpleDateFormat("dd.MM.yyyy").format(System.currentTimeMillis()));
			newVProject.setDescription(project.getName()+" от "+new SimpleDateFormat("dd.MM.yyyy").format(System.currentTimeMillis()));
			newVProject.setBPM_Parent_ID(project.get_ID());
			newVProject.set_ValueOfColumn("C_Year_ID", project.get_Value("C_Year_ID"));
			newVProject.setBPM_VersionBudget_ID(project.getBPM_VersionBudget_ID());
			newVProject.setAGR_Dispatcher_ID(1000003);
			newVProject.setAD_Org_ID(0);
			newVProject.setisWork(true);
			newVProject.setisActual(true);
			newVProject.save();
			
			BPM_Project_ID = newVProject.get_ID();
			setBPM_Project_ID(BPM_Project_ID);
			DB.executeUpdate("UPDATE BPM_Project SET IsWork='N' WHERE BPM_Project_ID = "+project.get_ID(),null);
		}
		//
		
		FormBP foBP = (FormBP) session.get(FormBP.sessionKey);
		collection=foBP.getCollection();
		if (    ( collection.AD_Org_ID!=AD_Org_ID ) 
			 || ( collection.form.get_ID()!=BPM_Form_ID )
			 || ( collection.BPM_Project_ID!=BPM_Project_ID ) 
		   )
			 collection = 
				new DSR_DataCollection(BPM_Form_ID,	BPM_Project_ID, AD_Org_ID, AD_Client_ID, null);
		setCollection(collection);
		setMessageError(err.toString());
		if (err.length() != 0)
			return NONE;

		updateValues(foBP);
		
		return INPUT;
	}

	public HttpServletRequest request() {
		return request;
	}

	public void setCollection(DSR_DataCollection collection) {
		this.collection = collection;
	}

	public void setServletRequest(HttpServletRequest request) {
		this.request = request;
	}

	public void setServletResponse(HttpServletResponse response) {
		this.response = response;
	}

	private boolean updateValues(FormBP fb) {
		StringBuffer sql = new StringBuffer("");
		StringBuffer value = new StringBuffer("");
		StringBuffer upd = new StringBuffer("update "
				+ I_BPM_FormValues.Table_Name);
		StringBuffer where = new StringBuffer(" where BPM_Project_ID="
				+ BPM_Project_ID + "\n and AD_Client_ID=" + AD_Client_ID 
				+ "\n and AD_Org_ID=" + AD_Org_ID
				+ "\n and BPM_Form_ID=" + BPM_Form_ID);



		DSR_Cell cell = null;
		Timestamp updated = new Timestamp(System.currentTimeMillis());


		for (int i = 0; i < collection.size(); i++) {

			for (int j = 0; j < collection.getRow(i).size(); j++) {
				cell = collection.getRow(i).getCell(j);
				if ("NaN".equals(cell.getValue())) cell.setValue("0");  
				if ((cell==null)||(cell.isRow)) continue;
				value.setLength(0);
				value.append(((request.getParameter("cell_Row" + (i+1) + "_Col" + j)).replaceAll(" ", "")).replaceAll(",", "."));
				if ("NaN".equals(value.toString())) { value.setLength(0); value.append("0");};  
				if ((value.toString().equals(cell.getValue()))) continue;
				if (cell.getBPM_FormValues_ID()==0) {
					MBPMFormValues formValues = new MBPMFormValues(Env.getCtx(), 0, null);
					formValues.set_ValueOfColumn("AD_Client_ID", AD_Client_ID);
					formValues.setAD_Org_ID(AD_Org_ID);
					formValues.setBPM_Form_ID(BPM_Form_ID);
					formValues.setBPM_FormCell_ID(cell.getBPM_FormCell_ID());
					formValues.setBPM_FormLine_ID(cell.getBPM_FormLine_ID());
					formValues.setBPM_FormColumn_ID(cell.getBPM_FormColumn_ID());
					formValues.setBPM_VersionBudget_ID(fb.BPM_VersionBudget_ID);
					formValues.setBPM_Project_ID(BPM_Project_ID);
					if (AD_Org_ID==0) {
						BigDecimal bCellValue = new BigDecimal(value.toString()).subtract(new BigDecimal(cell.getValue()));
						value.setLength(0);
						value.append(bCellValue);
						formValues.set_ValueOfColumn("AD_Org_ID", 1000007);
						formValues.setAlternateValue(bCellValue.toString());
					}
					formValues.setCellValue(value.toString());
					formValues.saveEx();
					cell.setBPM_FormValues_ID(formValues.get_ID());
				} else {
					sql.setLength(0);
					if (AD_Org_ID==0) {
						BigDecimal bCellValue = new BigDecimal(value.toString()).subtract(new BigDecimal(cell.getValue()));
						sql.append(upd).append(" set cellValue='");
						sql.append(bCellValue+ "'");
						sql.append(", AD_Org_ID = 1000007 ");
					} else {
						sql.append(upd).append(" set cellValue='");
						sql.append(value+ "'");
					}
					
					sql.append(" , updated='" + updated + "'"+ " , updatedby="+Env.getCtx().getProperty("#AD_User_ID"));
					sql.append(where).append(
						"\n and BPM_FormLine_ID=" + cell.getBPM_FormLine_ID()
								+ "\n and BPM_FormColumn_ID="
								+ cell.getBPM_FormColumn_ID());
					DB.executeUpdate(sql.toString(), null);
				}
				cell.setValue(value.toString());
			}

		}
		return true;
	}

	public int getAD_Client_ID() {
		return AD_Client_ID;
	}

	public void setAD_Client_ID(int AD_Client_ID) {
		this.AD_Client_ID = AD_Client_ID;
	}

	public int getAD_Org_ID() {
		return AD_Org_ID;
	}

	public void setAD_Org_ID(int AD_Org_ID) {
		this.AD_Org_ID = AD_Org_ID;
	}

	public int getBPM_VersionBudget_ID() {
		return BPM_VersionBudget_ID;
	}

	public void setBPM_VersionBudget_ID(int BPM_VersionBudget_ID) {
		this.BPM_VersionBudget_ID = BPM_VersionBudget_ID;
	}

	public int getBPM_Project_ID() {
		return BPM_Project_ID;
	}

	public void setBPM_Project_ID(int BPM_Project_ID) {
		this.BPM_Project_ID = BPM_Project_ID;
	}
	
	public void setAD_User_ID(int AD_User_ID) {
		this.AD_User_ID = AD_User_ID;
	}

	public int getAD_User_ID() {
		return AD_User_ID;
	}	

	public String getDocStatus() {
		return  MRefList.getListName(Env.getCtx(), 1000149, DocStatus); 
	}	

    public boolean getReadOnly() {
        return ReadOnly;
    }

	public void setBPM_Form_ID(int BPM_Form_ID) {
		this.BPM_Form_ID = BPM_Form_ID;
	}

	public int getBPM_Form_ID() {
		return BPM_Form_ID;
	}
	public LinkedHashMap<Integer, String> getProjectList() {
		return projectList;
	}
	
	public int getBPM_SubsProject_ID(){
		return BPM_SubsProject_ID;
	}
	
	public void setBPM_SubsProject_ID(int BPM_SubsProject_ID){
		this.BPM_SubsProject_ID = BPM_SubsProject_ID;
	}
	private void loadProjects(Properties ctx) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = "SELECT C_Project_ID, Name FROM " + I_C_Project.Table_Name
				+ " WHERE IsActive='Y' AND C_Project_ID !=1000000 AND AD_Client_ID = "+ AD_Client_ID +
				"ORDER BY Name";

		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				this.projectList.put(rs.getInt("c_project_id"), rs.getString("name"));
			}

		} catch (SQLException e) {
			log.log(Level.INFO, "product", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
	} // loadProjects
	
	public void setProjectList() {
		loadProjects(Env.getCtx());
	}
}
